<#assign object = doc.object>
package ${object.package}.service.base;
/**
* All the generated SQL statements for ${object.name} go in here.
* If you have custom queries, or you want to override existing ones, 
* best put them in here ${object.package}.service.${object.name}.
<#if object.trashcan[0]?? && object.trashcan == "true">
* <br>WARNING ${object.name} IMPLEMENTS TRASHCAN!!<br>
* Therefore, remember to suffix every where clause with 'AND roDeleted=false'.
</#if>
* @author Redora
* @see ${object.package}.service.${object.name}SQL
*/
public class ${object.name}SQLBase {

	/**
  	* The most important fields used for a tabular layout.
  	*/
  	public final static String TABLE_FIELDS = "id," +
		<#list object.tableScope?children as att>
		<#if att?node_name != "object">
    	"${att.@fieldName}," +
		</#if>
	</#list>
    	"creationDate,updateDate";

  	/**
  	* All the fields except the related children.
  	*/
  	public final static String FORM_FIELDS = "id," +
	<#list object.formScope?children as att>
		<#if att?node_name != "object">
    	"${att.@fieldName}," +
		</#if>
	</#list>
    	"creationDate,updateDate";

  	/**
  	* All the fields that are usually needed in the drop list as defined in the model.
  	*/
  	public final static String LIST_FIELDS = "id," +
    <#list object.listScope?children as att>
    	"${att.@fieldName}," +
    <#t>
    </#list>
    	"creationDate,updateDate";
    
    /**
  	* All the fields that are lazy loading as defined in the model.
  	*/
  	public final static String LAZY_FIELDS = "id," +
    <#list object.lazyScope?children as att>
    	<#if att?node_name != "object">
    	"${att.@fieldName}," +
    	</#if>
    <#t>
    </#list>
    	"creationDate,updateDate";

  	/** delete from ${object.name} where id = ? */
  	public final static String DELETE = "delete from ${object.name} where id = ?";

<#if object.trashcan[0]?? && object.trashcan == "true">
  	public final static String FIND_TRASH = "select " + TABLE_FIELDS + " from ${object.name} where roDeleted = true";

  	public final static String FIND_TRASH_BY_ID = "select " + FORM_FIELDS + " from ${object.name} where roDeleted = true and id = ?";

  	public final static String TRASH = "update ${object.name} set roDeleted = ? where id = ?";

</#if>

  	public final static String FIND_BY_ID_FORM = "select " + FORM_FIELDS + " from ${object.name} where id = ? <#if object.trashcan[0]?? && object.trashcan == "true">and roDeleted = false</#if>";
  	public final static String FIND_BY_ID_TABLE = "select " + TABLE_FIELDS + " from ${object.name} where id = ? <#if object.trashcan[0]?? && object.trashcan == "true">and roDeleted = false</#if>";

  	public final static String FIND_ALL_TABLE = "select " + TABLE_FIELDS + " from ${object.name} <#if object.trashcan[0]?? && object.trashcan == "true">where roDeleted = false </#if>order by 1 desc";
  	public final static String FIND_ALL_LIST = "select " + LIST_FIELDS + " from ${object.name} <#if object.trashcan[0]?? && object.trashcan == "true">where roDeleted = false </#if>order by 1 desc";

<#list object.attributes?children as att>
    <#if (att?node_name == "long" && att.@name?ends_with("Id"))>
    <@relation att=att />
  	public static final String FIND_BY_${relation_name?upper_case}_LIST = "select " + LIST_FIELDS + " from ${object.name} where ${relation_name} = ? <#if object.trashcan[0]?? && object.trashcan == "true">and roDeleted = false </#if>order by 1";
  	public static final String FIND_BY_${relation_name?upper_case}_TABLE = "select " + TABLE_FIELDS + " from ${object.name} where ${relation_name} = ? <#if object.trashcan[0]?? && object.trashcan == "true">and roDeleted = false </#if>order by 1";

  	public static final String DELETE_BY_${relation_name?upper_case} = "delete from ${object.name} where ${relation_name} = ?";

        <#if object.trashcan[0]?? && object.trashcan == "true">
  	public static final String TRASH_BY_${relation_name?upper_case} = "update ${object.name} set roDeleted = ? where ${relation_name} = ?";
        </#if>
    </#if>
</#list>

<#list object.attributes.enum as att>
  	/**
  	* where ${att.@fieldName} = ?<br />
    <#if object.trashcan[0]?? && object.trashcan == "true">
  	* and roDeleted = false<br />
    </#if>
  	* order by 1
  	*/
  	public static final String FIND_BY_${att.@fieldName?upper_case}_TABLE = "select " + TABLE_FIELDS + " from ${object.name} where ${att.@fieldName} = ?<#if object.trashcan[0]?? && object.trashcan == "true"> and roDeleted = false</#if> order by 1";
  
  	public static final String FIND_BY_${att.@fieldName?upper_case}_LIST = "select " + LIST_FIELDS + " from ${object.name} where ${att.@fieldName} = ?<#if object.trashcan[0]?? && object.trashcan == "true"> and roDeleted = false</#if> order by 1";
</#list>

<#list doc["/object/attributes/xml | /object/attributes/html"] as att>
  	/**
  	* where id = ?<br />
<#if object.trashcan[0]?? && object.trashcan == "true">
  	* and roDeleted = false<br />
</#if>
  	*/
  	public static final String FIND_${att.@fieldName?upper_case}_BY_ID = "select ${att.@fieldName} from ${object.name} where id = ?<#if object.trashcan[0]?? && object.trashcan == "true"> and roDeleted = false</#if>";
</#list>

<#list doc["/object/attributes/*[@finder='true']"] as att>
	/**
	* where ${att.@fieldName} = ?<br />
<#if object.trashcan[0]?? && object.trashcan == "true">
    * and roDeleted = false<br />
</#if>
	* order by 1
	*/
	public static final String FIND_BY_${att.@fieldName?upper_case}_TABLE = "select " + TABLE_FIELDS + " from ${object.name} where ${att.@fieldName} = ?<#if object.trashcan[0]?? && object.trashcan == "true"> and roDeleted = false</#if> order by 1";
	
	public static final String FIND_BY_${att.@fieldName?upper_case}_LIST = "select " + LIST_FIELDS + " from ${object.name} where ${att.@fieldName} = ?<#if object.trashcan[0]?? && object.trashcan == "true"> and roDeleted = false</#if> order by 1";
</#list>

	public static final String FETCH_LAZY_BY_ID = "select " + LAZY_FIELDS + " from ${object.name} where id = ?<#if object.trashcan[0]?? && object.trashcan == "true"> and roDeleted = false</#if>";

  	/** Create table statement for MySQL */
  	public final static String CREATE_TABLE = "create table ${object.name} ("
    	+ "id bigint not null auto_increment, "
<#list object.formScope?children as att>
    <#if att?node_name != "object" && att?node_type == "element">
    	+ "${att.@fieldName} <#t>
        <#if att?node_name == "boolean">
    	tinyint(1) <#t>
        <#elseif att?node_name == "date">
    	date <#t>
        <#elseif att?node_name == "datetime">
    	datetime <#t>
        <#elseif att?node_name == "enum">
    	enum(<#t>
            <#list att.element as value>
      	'${value.@name}'<#if value_has_next>,</#if><#t>
            </#list>
    	) <#t>
        <#elseif att?node_name=="integer">
    	int <#t>
        <#elseif att?node_name=="long">
    	bigint <#t>
    	<#elseif att?node_name=="double">
    	double <#t>
        <#elseif att?node_name=="string">
            <#if att.@maxlength?number &gt; 16000000>
    	longtext <#t>
            <#elseif att.@maxlength?number &gt; 65000>
    	mediumtext <#t>
            <#else>
    	varchar(${att.@maxlength}) <#t>
            </#if>
        <#elseif att?node_name == "xml" || att?node_name == "html">
    	longtext <#t>
        </#if>
        <#if att.@notnull[0]?? && att.@notnull == "true">
    	not null <#t>
        </#if>
    	,"<#t>
    
    </#if>
</#list>
<#if object.trashcan[0]?? && object.trashcan == "true">
    	+ "roDeleted boolean not null default false,"
</#if>
    	+ "creationDate timestamp not null, updateDate timestamp null,"
    	+ " primary key (id)"
<#list object.attributes?children as att>
    <#if (att?node_name == "long" && att.@name?ends_with("Id")) || att?node_name == "enum">
    	+ ", index ${object.name}_${att.@name[0]!att.@class?uncap_first} (${att.@name[0]!att.@class?uncap_first})"
    </#if>
</#list>
<#list doc["/object/attributes/*[@finder='true']"] as att>
    	+ ", index ${object.name}_${att.@fieldName} (${att.@fieldName})"
</#list>
    	+ ")";<#t>
	
	//TODO not needed when we have pagination 
  	public static final String TEST = FIND_ALL_TABLE + " limit 10";
	
<#list doc["/object/attributes/set[@multiplicity='n-to-m']"] as att>
	<@relation_table att=att />
  	/** Create table of set statement for MySQL */
  	public final static String CREATE_${relation_table_name?upper_case}_TABLE = "create table ${relation_table_name} ("
    	+ "id bigint not null auto_increment,"
    	+ "${object.name}Id bigint not null,"
    	+ "${att.@class}Id bigint not null,"
<#if object.trashcan[0]?? && object.trashcan == "true">
		+ "roDeleted boolean not null default false,"
</#if>
    	+ "creationDate timestamp not null,updateDate timestamp null,"
    	+ "primary key (id)"
    	+ ", index ${relation_table_name}_${object.name}Id (${object.name}Id)"
    	+ ", index ${relation_table_name}_${att.@class}Id (${att.@class}Id)"    
    	+ ")";
  
  	/** delete from ${relation_table_name?upper_case} where ${object.name}Id = ? and ${att.@class}Id = ? */
  	public final static String DELETE_${relation_table_name?upper_case}_RELATION = "delete from ${relation_table_name} where ${object.name}Id = ? and ${att.@class}Id = ?";

<#if object.trashcan[0]?? && object.trashcan == "true">
  	public final static String TRASH_${relation_table_name?upper_case}_RELATION = "update ${relation_table_name?upper_case} set roDeleted = true where ${object.name}Id = ? and ${att.@class}Id = ?";
</#if>
  
  	public final static String DELETE_${relation_table_name?upper_case}_RELATION_BY_${object.name?upper_case}ID = "delete from ${relation_table_name} where ${object.name}Id = ?";

<#if object.trashcan[0]?? && object.trashcan == "true">
  	public final static String TRASH_${relation_table_name?upper_case}_RELATION_BY_${object.name?upper_case}ID = "update ${relation_table_name} set roDeleted = ? where ${object.name}Id = ?";
</#if>
  
  	public final static String INSERT_${relation_table_name?upper_case}_RELATION = "insert into ${relation_table_name} (${object.name}Id,${att.@class}Id,creationDate) values (?,?,now())";
  
  	public final static String FIND_${object.name?upper_case}_BY_${att.@class?upper_case}ID_LIST = "select " + LIST_FIELDS + " from ${object.name} where id in (select ${object.name}Id from ${relation_table_name} where ${att.@class}Id = ?)<#if object.trashcan[0]?? && object.trashcan == "true"> and roDeleted = false</#if>";
  	public final static String FIND_${object.name?upper_case}_BY_${att.@class?upper_case}ID_TABLE = "select " + TABLE_FIELDS + " from ${object.name} where id in (select ${object.name}Id from ${relation_table_name} where ${att.@class}Id = ?)<#if object.trashcan[0]?? && object.trashcan == "true"> and roDeleted = false</#if>";
</#list>
}
